﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

using NORM.Common;
using NORM.Entity;
using NORM.SQLObject;
using System.Data.SQLite;
using System.Reflection;
using NORM.Configure;
using System.Text.RegularExpressions;

namespace NORM.DataBase
{
    /// <summary>
    /// SQLiteDataBase
    /// </summary>
    internal class SQLiteDataBase : DataBase, IDataBase
    {
        protected bool TransactionBegin = false;
        protected SQLiteHelper sqllite = new SQLiteHelper();

        private System.Data.SQLite.SQLiteTransaction tran;
        private System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();

        internal SQLiteDataBase()
        {
            this._sql = string.Empty;
            try
            {
                this._connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
                //解析连接字符串，解密
                if (string.IsNullOrEmpty(this._connectionString))
                    throw new Exception("连接字符串不能为空");
                SectionString[] SectionsString = Parse(this._connectionString);
                this._connectionString = Join(SectionsString);
                this._dataBaseType = DataBaseTypes.SqliteDataBase;
                //初始化 conn
                this.conn = new System.Data.SQLite.SQLiteConnection(_connectionString);
            }
            catch (Exception ex)
            {
                throw new NORMException(ExceptionType.ConfigException, ex.Message);
            }
        }

        internal SQLiteDataBase(string connectionString)
        {
            this._sql = string.Empty;
            this._connectionString = connectionString; //System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
            //解析连接字符串，解密
            SectionString[] SectionsString = Parse(this._connectionString);
            this._connectionString = Join(SectionsString);
            this._dataBaseType = DataBaseTypes.SqliteDataBase;
            //初始化 conn
            this.conn = new System.Data.SQLite.SQLiteConnection(_connectionString);
           
        }

        #region 私有方法

        int execute(OQL oql)
        {
            int val = -1;

            SetCommand(oql);

            if (TransactionBegin)
                val = sqllite.Execute(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                val = sqllite.Execute(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            return val;
        }

        bool insert<T>(T model) where T : EntityBase
        {
            bool rvl = false;
            if (model != null)
            {
                OQL oql = OQL.Insert(model).Values().End;

                if (execute(oql) > 0)
                {
                    rvl = true;
                }

            }
            return rvl;
        }

        bool update<T>(T model) where T : EntityBase
        {
            bool rvl = false;

            if (model != null)
            {
                OQL oql = OQL.Update(model).Set().End;

                if (execute(oql) > 0)
                {
                    rvl = true;
                }
            }            

            return rvl;
        }

        bool delete<T>(T model) where T : EntityBase
        {
            bool rvl = false;

            if (model != null)
            {
                List<Condition> conditions = new List<Condition>();

                Type _type = model.GetType();
                PropertyInfo[] properties = CacheProperties.cacheInstance.GetProperties(_type); //_type.GetProperties();
                foreach (PropertyInfo pi in properties)
                {
                    if (pi.Name != "TableName" && pi.Name != "Properties")
                    {
                        object valud_d = pi.GetValue(model, null);
                        if (valud_d != null && DBNull.Value != valud_d)
                        {
                            Condition c = new Condition();
                            c.Field = pi.Name;
                            c.Value = valud_d;
                            conditions.Add(c);
                        }
                    }
                }

                OQL oql = OQL.Delete(model).Where(conditions.ToArray()).End;

                if (execute(oql) > 0)
                {
                    rvl = true;
                }

            }

            return rvl;
        }

        #endregion

        #region 设置Command命令
        public void SetCommand(OQL oql)
        {
            _sql = oql.ToString();
            Command =new OsqlCommand();

            foreach (var model in oql.modelArray)
            {
                string classTypeName = "";
                object[] attrs = model.GetType().GetCustomAttributes(false);// typeof(T)
                if (attrs != null && attrs.Length > 0)
                {
                    foreach (object attr in attrs)
                    {
                        if (attr.GetType().Equals(typeof(DescribeAttribute)))
                        {
                            classTypeName = attr.ToString();
                            break;
                        }
                    }
                }
                switch (classTypeName)
                {
                    case "View":
                        _sql = _sql.Replace("[", "").Replace("]", "");
                        break;
                    case "Table":
                        break;
                }
            }

            Command.CommandText = _sql;

            List<SQLiteParameter> _params = null;   

            if (oql.Parameters != null)
            {
                OsqlParameter[] osqlparams = oql.Parameters;
                switch (DataBaseType)
                {
                    case DataBaseTypes.SqliteDataBase:
                        _params = new List<SQLiteParameter>();
                        foreach (OsqlParameter p in osqlparams)
                        {
                            SQLiteParameter p1 = new SQLiteParameter("@" + p.Name, p.Value);
                            _params.Add(p1);
                        }
                        Command.Parameters = _params.ToArray();
                        break;
                }
            }

            base.validateOqlend(oql);
            //oql.Dispose();

        }

        public void SetCommand(string sql, params DbParameter[] cmdParms)
        {
            this._sql = sql;

            Command = new OsqlCommand();             
            Command.CommandText = _sql;

            List<SQLiteParameter> _params = null;

            if (cmdParms != null)
            {                 
                switch (DataBaseType)
                {
                    case DataBaseTypes.SqliteDataBase:
                        _params = new List<SQLiteParameter>();
                        foreach (var p in cmdParms)
                        {                           
                            SQLiteParameter p1 = new SQLiteParameter("@" + p.ParameterName, p.Value);
                            _params.Add(p1);
                        }
                        Command.Parameters = _params.ToArray();
                        break;
                }
            }

        }
        #endregion

        #region 数据库对象

        public override DataTable GetDataBaseObject()
        {
            return sqllite.GetDbSchema(this._connectionString);
        }

        public override DataTable GetDataBaseObject(string collectionName)
        {
            return sqllite.GetDbSchema(this._connectionString, collectionName);
        }

        public override DataTable GetTableObject(string name, DataBaseSchema schema)
        {
            DataTable dt = new DataTable();
            switch (schema)
            {
                case DataBaseSchema.Table:
                    dt = sqllite.GetTableColumns(this._connectionString, name);
                    break;
                case DataBaseSchema.View:
                    dt = sqllite.GetTableColumns(this._connectionString, name);
                    break;
            }
            return dt;
        }

        public override DataTable GetTableObject(string name)
        {
            return sqllite.GetTableColumns(this._connectionString, name);
        }

        public override DataTable GetTableIndexes(string tableName)
        {
            string strSql = "SELECT name as index_name , sql as index_description , sql as index_keys FROM ( SELECT * FROM sqlite_master UNION ALL  ";
            strSql += " SELECT * FROM sqlite_temp_master) WHERE type='index' and tbl_name='"+tableName+"' ORDER BY name";
            DataTable dt=sqllite.GetDataTable(this._connectionString, CommandType.Text, strSql, null);
            DataTable result = dt.Clone();
            foreach (DataRow dr in dt.Rows)
            {
                DataRow row = result.NewRow();
                row["index_name"] = dr["index_name"];
                row["index_description"] = dr["index_description"];

                Regex regex = new Regex(@"\(.*?\)", RegexOptions.IgnoreCase);
                MatchCollection matches = regex.Matches(dr["index_description"] + "");
                StringBuilder sb = new StringBuilder();//存放匹配结果

                foreach (Match match in matches)
                {
                    string value = match.Value.Trim('(', ')');
                    sb.AppendLine(value);
                }

                //Regex rex = new Regex("(?<MYSTR>（.*）)");
                //sb.Append(rex.Match(dr["index_description"] + "").Groups["MYSTR"].ToString());

                row["index_keys"] = sb.ToString();

                result.Rows.Add(row);
                 
            }
            dt.Dispose();
            return result;
        }

        #endregion

        #region 事务操作

        public override void BeginTransaction()
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            tran = conn.BeginTransaction();
            sqllite.tran = tran;
            TransactionBegin = true;
            _surplusTransTime++;
        }

        public override int Commit()
        {
            try
            {
                tran.Commit();
                sqllite.tran = null;
            }
            catch (Exception ex)
            {
              
            }
            TransactionBegin = false;
            _surplusTransTime--;
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return 1;
        }

        public override void RollBack()
        {
            try
            {
                tran.Rollback();
                sqllite.tran = null;
            }
            catch (Exception ex)
            {

            }
            TransactionBegin = false;
            _surplusTransTime--;
            if (conn.State == ConnectionState.Open)
                conn.Close();
        }

        public override bool TestConnect(out string message)
        {
            var rvl = base.TestConnect(out message);
            try
            {
                if (conn != null && conn.State != ConnectionState.Open)
                {
                    conn.Open();
                    message = "连接成功";
                    rvl = true;
                }
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            return rvl;
        }

        #endregion

        #region  执行SQL脚本

        public override int Execute(CommandType cmdType, string cmdText, params OsqlParameter[] cmdParms)
        {
            //return sqllite.Execute(_connectionString, cmdType, cmdText, cmdParms);

            DbParameter[] parameters = ParametersTransport(cmdParms);

            SetCommand(cmdText, parameters);

            int val = -1;
            if (TransactionBegin)
                val = sqllite.Execute(conn, cmdType, cmdText, parameters);
            else
                val = sqllite.Execute(this._connectionString, cmdType, cmdText, parameters);

            return val;
        }

        public override DataSet QueryDataSet(CommandType cmdType, string cmdText, params OsqlParameter[] cmdParms)
        {
            //return sqllite.GetDataSet(_connectionString, cmdType, cmdText, cmdParms);

            DbParameter[] parameters = ParametersTransport(cmdParms);

            SetCommand(cmdText, parameters);

            DataSet val = null;
            if (TransactionBegin)
                val = sqllite.GetDataSet(conn, cmdType, cmdText, parameters);
            else
                val = sqllite.GetDataSet(this._connectionString, cmdType, cmdText, parameters);

            return val;
        }

        public override DataTable QueryTable(string cmdText, OsqlParameter[] cmdParms = null)
        {
            return QueryDataSet(CommandType.Text, cmdText, cmdParms).Tables[0];
        }

        public override DataTable QueryTable(CommandType cmdType, string cmdText, OsqlParameter[] cmdParms = null)
        {
            return QueryDataSet(cmdType, cmdText, cmdParms).Tables[0];
        }

        public override bool UpdateDataSet(DataSet dataSet, string srcTable)
        {
            bool rvl = false;
            if (TransactionBegin)
                rvl = sqllite.UpdateDataSet(conn, dataSet, srcTable);
            else
                rvl = sqllite.UpdateDataSet(this._connectionString, dataSet, srcTable);
            return rvl;
        }

        #endregion

        #region 执行OQL

        public override int Execute(OQL oql)
        {            
            return this.execute(oql);
        }

        public override bool Insert<T>(T model)
        {
            return this.insert<T>(model);
        }

        public override bool Update<T>(T model)
        {
            return this.update<T>(model);
        }

        public override bool Delete<T>(T model)
        {
            return this.delete<T>(model);
        }

        public override bool Delete<T>(object PrimaryKeyValue)
        {
            T model = (T)Activator.CreateInstance(typeof(T));
            System.Reflection.PropertyInfo pi = typeof(T).GetProperty(model.PrimaryKey[0]);
            if (pi == null) throw new Exception("没有设置主键");            
          
            OQL oql = OQL.Delete(model).Where(new Condition[] { new Condition() { Field = pi.Name, Value = PrimaryKeyValue } }).End;

            return this.execute(oql) > 0 ? true : false;
        }

        public override List<T> QueryList<T>(OQL oql, PageLimit page)
        {
            SetCommand(oql);  

            string _total_sql = "SELECT COUNT(1) FROM (" +
                Command.CommandText.TrimEnd(';') + " ) tn ";

            var count = sqllite.GetScalar(conn, CommandType.Text, _total_sql, Command.Parameters);

            int recordCount = 0;
            if (!Int32.TryParse(count + "", out recordCount))
            {
                return new List<T>();
            }

            page.RecordCount = recordCount;
            page.PageCount = (int)(decimal.Ceiling((decimal)(page.RecordCount * 1.0 / page.PageSize)));

            if (page.PageIndex <= 1) page.PageIndex = 1;
            if (page.PageIndex >= page.PageCount) page.PageIndex = page.PageCount;

            int startIndex = (page.PageIndex - 1) * page.PageSize;
            int endIndex = startIndex + page.PageSize;

            Command.CommandText = Command.CommandText.TrimEnd(';') + " Limit " + page.PageSize + " Offset  " + startIndex + "";

            DataTable dt = new DataTable();
            if (TransactionBegin)
                dt = sqllite.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                dt = sqllite.GetDataTable(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            var list = ConvertTo.ConvertToList<T>(dt);

            page.DataSourceView = dt.DefaultView;
            oql.RecordCount = page.RecordCount;

            return list;
        }

        public override List<T> QueryList<T>(OQL oql)
        {
            SetCommand(oql);

            DataTable dt = new DataTable();
            if (TransactionBegin)
                dt = sqllite.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                dt = sqllite.GetDataTable(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            var list = ConvertTo.ConvertToList<T>(dt);
            oql.RecordCount = list.Count;

            return list;
        }

        public override T QueryObject<T>(OQL oql)
        {
            SetCommand(oql);

            T t = null; //(T)Activator.CreateInstance(typeof(T));// new T();
            Command.CommandText = Command.CommandText.Replace("Limit 1", "").TrimEnd(';') + " Limit 1";
            DataTable dt = new DataTable();
            if (TransactionBegin)
                dt = sqllite.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                dt = sqllite.GetDataTable(_connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            List<T> list = ConvertTo.ConvertToList<T>(dt);
            foreach (T _t in list)
                t = _t;

            return t;           
        }

        public override T QueryModel<T>(object PrimaryKeyValue)
        {
            T t = (T)Activator.CreateInstance(typeof(T));// new T();

            if (t.PrimaryKey.Count <= 0)
                throw new Exception(t.TableName + "表还没有设置主键");

            OQL oql = new OQL(t).Select().Where(new Condition[] { 
                new Condition(){Field = t.PrimaryKey[0].ToString(), Comparison = "=", Value =PrimaryKeyValue } })
                .OrderBy(new string[] { t.PrimaryKey[0].ToString() }).End;

            SetCommand(oql); 
 
            t = QueryObject<T>(oql);

            return t;
        }

        public override DataTable QueryTable(OQL oql, PageLimit page = null)
        {
            DataTable dt = null;
            try
            {
                SetCommand(oql);

                if (page != null)
                {
                    string _total_sql = "SELECT COUNT(1) FROM (" +
                   Command.CommandText.TrimEnd(';') + " ) tn ";

                    var count = sqllite.GetScalar(conn, CommandType.Text, _total_sql, Command.Parameters);

                    int recordCount = 0;
                    if (!Int32.TryParse(count + "", out recordCount))
                    {
                        return new DataTable();
                    }

                    page.RecordCount = recordCount;
                    page.PageCount = (int)(decimal.Ceiling((decimal)(page.RecordCount * 1.0 / page.PageSize)));

                    if (page.PageIndex <= 1) page.PageIndex = 1;
                    if (page.PageIndex >= page.PageCount) page.PageIndex = page.PageCount;

                    int startIndex = (page.PageIndex - 1) * page.PageSize;
                    int endIndex = startIndex + page.PageSize;

                    Command.CommandText = Command.CommandText.TrimEnd(';') + " Limit " + page.PageSize + " Offset  " + startIndex + "";

                }

                if (TransactionBegin)
                    dt = sqllite.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
                else
                    dt = sqllite.GetDataTable(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        #endregion

        #region 参数转换

        private DbParameter[] ParametersTransport(OsqlParameter[] cmdParms)
        {
            SQLiteParameter[] parameters = new SQLiteParameter[cmdParms.Length];

            for (int i = 0; i < cmdParms.Length; i++)
            {
                OsqlParameter parameter = cmdParms[i];
                parameters[i] = new SQLiteParameter(parameter.Name, parameter.Value);
                DbType parameterDbType = DbType.String;
                switch (parameter.DataType)
                {
                    case OsqlParameterDataType.VarChar:
                    case OsqlParameterDataType.String:
                        parameterDbType = DbType.String;
                        break;
                    case OsqlParameterDataType.Char:
                        parameterDbType = DbType.String;
                        break;
                    case OsqlParameterDataType.Int:
                        parameterDbType = DbType.Int32;
                        break;
                    case OsqlParameterDataType.Integer:
                        parameterDbType = DbType.Int64;
                        break;
                    case OsqlParameterDataType.Bool:
                        parameterDbType = DbType.Boolean;
                        break;                    
                    case OsqlParameterDataType.Guid:
                        parameterDbType = DbType.Guid;
                        break;
                    case OsqlParameterDataType.Numeric:
                        parameterDbType = DbType.Decimal;
                        break;
                    case OsqlParameterDataType.DateTime:
                        parameterDbType = DbType.DateTime;
                        break;
                    case OsqlParameterDataType.DateTime2:
                        parameterDbType = DbType.DateTime2;
                        break;
                    case OsqlParameterDataType.Date:
                        parameterDbType = DbType.Date;
                        break;
                    case OsqlParameterDataType.Time:
                        parameterDbType = DbType.Time;
                        break;
                    case OsqlParameterDataType.Text:
                        parameterDbType = DbType.String;
                        break;
                    case OsqlParameterDataType.Byte:
                        parameterDbType = DbType.Byte;
                        break;
                    case OsqlParameterDataType.Stream:
                        parameterDbType = DbType.Binary;
                        break;
                }
                parameters[i].DbType = parameterDbType;
                parameters[i].Direction = parameter.Direction;
            }
            return parameters;
        }

        #endregion

    }
}
